﻿using MYear.ODA;
using MYear.ODA.Cmd;
using MYear.ODA.Model;
using System;
using System.Collections.Generic;
using MYear.ODA.Ctx;

namespace MYear.Demo
{
    public class SelectDemo
    {
        [Demo(Demo = FuncType.Select, MethodName = "Select", MethodDescript = "简单查询")]
        public static object Select()
        { 
            ///ODA语法就SQL语法的直接映射,并尽量简法
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER(); 
            object data = U.Where(U.USER_NAME == "Lambda")
                  .And(U.IS_LOCKED == "N")
                  .And(U.STATUS == "O") 
                 .Select(U.USER_ACCOUNT, U.USER_PASSWORD.As("PWD"), U.USER_NAME, U.PHONE_NO, U.EMAIL_ADDR,U.ADDRESS,U.CREATED_DATE); 
            return data;
        }
        [Demo(Demo = FuncType.Select, MethodName = "SelectM", MethodDescript = "查询默认实体")]
        public static object SelectM()
        {
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            List<SYS_USER> data = U.Where(U.CREATED_BY == "Insert", U.IS_LOCKED == "N", U.STATUS == "O")
                .SelectM(U.USER_ACCOUNT, U.USER_NAME, U.PHONE_NO, U.EMAIL_ADDR, U.CREATED_DATE);
            return data;
        }
        [Demo(Demo = FuncType.Select, MethodName = "Select<>", MethodDescript = "查询并返回实体类型")]
        public static object SelectDefine()
        {
            ////返回的实体类型可以是任意自定义类型，并不一定是对应数据库的实体
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            List<SYS_USER> data = U.Where(U.CREATED_BY.Like("Import%"))
                  .And(U.IS_LOCKED == "N")
                  .And(U.STATUS == "O")
                  .And(U.EMAIL_ADDR.IsNotNull)
                 .Select<SYS_USER>(U.USER_ACCOUNT, U.USER_NAME, U.PHONE_NO, U.EMAIL_ADDR);
            return data;
        }

        [Demo(Demo = FuncType.Select, MethodName = "SelectPaging", MethodDescript = "查询分页")]
        public static object SelectPaging()
        {
            ODAContext ctx = BizContext.GetContext();
            int total = 0; 
            var U = ctx.SYS_USER();
            var data = U.Where(U.CREATED_BY.ContainLeft("Import"), U.IS_LOCKED == "N")
            .SelectM(0,20,out total, U.CREATED_BY,U.CREATED_DATE, U.USER_ACCOUNT, U.USER_NAME, U.PHONE_NO, U.EMAIL_ADDR); 
            return data;
        }
        [Demo(Demo = FuncType.Select, MethodName = "SelectFirst", MethodDescript = "查询第一行")]
        public static object SelectFirst()
        {
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var data = U.Where(U.CREATED_BY == "InsertModel", U.IS_LOCKED == "N", U.USER_NAME.IsNotNull)
            .SelectDynamicFirst(U.USER_ACCOUNT, U.USER_NAME, U.PHONE_NO, U.EMAIL_ADDR);

            string UserName = data.USER_NAME; ///属性 USER_NAME 与 USER_NAME 的ColumnName一致，如果没有数据则返回null
            return data; 
        }

        [Demo(Demo = FuncType.Select, MethodName = "SelectDynamic", MethodDescript = "返回动态数据模型")]
        public static object SelectDynamic()
        {
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var data = U.Where(U.CREATED_BY == "Lambda", U.IS_LOCKED == "N", U.EMAIL_ADDR.IsNotNull)
            .SelectDynamic(U.USER_ACCOUNT, U.USER_NAME, U.PHONE_NO, U.EMAIL_ADDR);

            string UserName = "";
            if (data.Count > 0)
                UserName =  data[0].USER_NAME; ///与 USER_NAME  的 ColumnName一致.
            return data;
        }

        [Demo(Demo = FuncType.Select, MethodName = "Distinct", MethodDescript = "去重复")]
        public static object Distinct()
        {
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var data = U.Where( U.IS_LOCKED == "N", U.EMAIL_ADDR.IsNotNull)
            .Distinct.Select(U.USER_ACCOUNT, U.USER_NAME, U.PHONE_NO, U.EMAIL_ADDR);
            return data;
        }

        [Demo(Demo = FuncType.Select, MethodName = "Join", MethodDescript = "连接查询")]
        public static object Join()
        {
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var R = ctx.SYS_ROLE();
            var UR = ctx.SYS_USER_ROLE();

            ///LeftJoin\InnerJoin\RightJoin 可以无限连接，但实际上每种数据库都有对SQL语句长度作限制。
            var data = U.InnerJoin(UR, U.USER_ACCOUNT == UR.USER_ACCOUNT, UR.STATUS == "O")
                .LeftJoin(R, UR.ROLE_CODE == R.ROLE_CODE, R.STATUS == "O")
                .Where(U.STATUS == "O",R.ROLE_CODE == "Administrator")
                 .Select<UserDefineModel>(U.USER_ACCOUNT.As("UserAccount"), U.USER_NAME.As("UserName"),R.ROLE_CODE.As("Role"), R.ROLE_NAME.As("RoleName"));
            return data;
        }
        [Demo(Demo = FuncType.Select, MethodName = "List", MethodDescript = "简单内连接")]
        public static object List()
        {
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var R = ctx.SYS_ROLE();
            var UR = ctx.SYS_USER_ROLE();
            var data =  U.ListCmd(UR,R)
                .Where(U.USER_ACCOUNT == UR.USER_ACCOUNT, 
                 UR.STATUS == "O",
                 UR.ROLE_CODE == R.ROLE_CODE,
                 R.STATUS == "O",
                 U.STATUS == "O",
                 R.ROLE_CODE == "Administrator")
                 .Select< UserDefineModel>(U.USER_ACCOUNT.As("UserAccount"), U.USER_NAME.As("UserName"),U.EMAIL_ADDR.As("Email"), R.ROLE_CODE.As("Role"), R.ROLE_NAME.As("RoleName"));

            return data;
        }

        [Demo(Demo = FuncType.Select, MethodName = "SubQuery", MethodDescript = "嵌套子查询")]
        public static object SubQuery()
        {
            ////嵌套子查询需要把一个查询子句转换成视图(ToView方法)，转换成视图之后可以把它视作普通的Cmd使用。
            ///视图里ViewColumns是视图字段的集合。
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var R = ctx.SYS_ROLE();
            var UR = ctx.SYS_USER_ROLE();

            var UA = ctx.GetCmd<SysUserAuthorization>();
            var RA = ctx.SYS_ROLE_AUTHORIZATION();

            var Admin = U.InnerJoin(UR, U.USER_ACCOUNT == UR.USER_ACCOUNT, UR.STATUS == "O")
                .InnerJoin(R, UR.ROLE_CODE == R.ROLE_CODE, R.STATUS == "O")
                .Where(U.STATUS == "O")
                .ToView(U.USER_ACCOUNT.As("SYS_USER"), U.USER_NAME, R.ROLE_CODE.As("SYS_ROLE"), R.ROLE_NAME); ////子查询

           var data =  Admin.InnerJoin(UA, UA.USER_ACCOUNT == Admin.ViewColumns[1],UA.IS_FORBIDDEN == "N")
                .InnerJoin(RA,RA.ROLE_CODE == Admin.ViewColumns[2],RA.IS_FORBIDDEN =="N") 
                .Where(Admin.ViewColumns[0] == "MYEAR_Insert",
                Admin.ViewColumns[2] == "Administrator")
                .Select(); 
            return data;
        }
        [Demo(Demo = FuncType.Select, MethodName = "Union", MethodDescript = "Union")]
        public static object Union()
        { 
            ///被Union或UnionAll的是视图。要求视图与查询的字段的数据库类型及顺序及数据一致（数据库本身的要求，非ODA要求)。
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER(); 
            var UR = ctx.SYS_USER_ROLE();  
            var RA = ctx.SYS_ROLE_AUTHORIZATION();
            var RS = ctx.SYS_RESOURCE();

            var U1 = ctx.SYS_USER();
            var UA = ctx.GetCmd<SysUserAuthorization>();
            var RS1 = ctx.SYS_RESOURCE();

            U.InnerJoin(UR, U.USER_ACCOUNT == UR.USER_ACCOUNT, UR.STATUS == "O")
                .InnerJoin(RA, RA.ROLE_CODE == UR.ROLE_CODE, RA.STATUS == "O")
                .InnerJoin(RS, RS.ID == RA.RESOURCE_ID, RS.STATUS == "O")
                .Where(U.USER_ACCOUNT == "User1");

            U1.InnerJoin(UA, U1.USER_ACCOUNT == UA.USER_ACCOUNT, UA.STATUS == "O")
                .InnerJoin(RS1, RS1.ID == UA.RESOURCE_ID , RS1.STATUS=="O")
                .Where(U1.USER_ACCOUNT == "User1");
 
            var data = U.Union(U1.ToView(U1.USER_ACCOUNT, U1.USER_NAME, UA.IS_FORBIDDEN,
                RS1.ID, RS1.RESOURCE_TYPE, RS1.RESOURCE_SCOPE, RS1.RESOURCE_LOCATION
                ))
                .Select(U.USER_ACCOUNT, U.USER_NAME, RA.IS_FORBIDDEN,
                RS.ID, RS.RESOURCE_TYPE, RS.RESOURCE_SCOPE, RS.RESOURCE_LOCATION
                ); 
            return data;
        }
        [Demo(Demo = FuncType.Select, MethodName = "OrderBy", MethodDescript = "查询排序")]
        public static object OrderBy()
        {
            ///OrderbyAsc 或OrderbyDesc 对数据按顺序或倒序排列，先给出的排序条件优先排。
            ///OrderbyAsc 或OrderbyDesc 参数可以是多个字段。
            ODAContext ctx = BizContext.GetContext();
            var RS = ctx.SYS_RESOURCE();
            var datra = RS.Where(RS.RESOURCE_TYPE == "WEB", RS.STATUS == "O")
                 .OrderbyDesc(RS.RESOURCE_INDEX)
                 .SelectM();
            return datra;
        }
        [Demo(Demo = FuncType.Select, MethodName = "Where", MethodDescript = "数据库查询条件语法")]
        public static object Where()
        {
            ///Join、Where、Having 查询参数：条件之间可用运算符 “|”(Or方法）或“&”(And方法)表明条件与条件之间的关系；
            ///如同时列出多个条件，则说明SQL语句要同时满足所有条件（即“And”关系）；
            ///Where和Having方法可以多次调用，每调一次SQL语句累加一个条件（And、Or、Groupby、OrderbyAsc、OrderbyDesc方法类同)；
            ///与 Where 方法同等级的 And 方法是等效的
            ///也就是说，数据筛选条件可以根据业务情况动态增加；
            /// IS NULL/ IS NOT NULL 条件可由字段直接带出，如：EMAIL_ADDR.IsNotNull 
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var UR = ctx.SYS_USER_ROLE();

            var data = U.InnerJoin(UR, U.USER_ACCOUNT == UR.USER_ACCOUNT, UR.STATUS == "O")
              .Where(U.STATUS == "O", U.EMAIL_ADDR.IsNotNull.Or(U.EMAIL_ADDR == "riwfnsse@163.com"), U.IS_LOCKED == "N")
              .Where(UR.ROLE_CODE.In("Administrator", "Admin", "PowerUser", "User", "Guest")) 
              .Groupby(UR.ROLE_CODE)
              .Having(U.USER_ACCOUNT.Count > 2)
              .OrderbyAsc(U.USER_ACCOUNT.Count)
              .Select(U.USER_ACCOUNT.Count.As("USER_COUNT"), UR.ROLE_CODE);

            //以下写法是等效的
            U.InnerJoin(UR, U.USER_ACCOUNT == UR.USER_ACCOUNT, UR.STATUS == "O");
            U.Where(U.STATUS == "O");
            U.Where(U.EMAIL_ADDR.IsNotNull.Or(U.EMAIL_ADDR == "riwfnsse@163.com"));
            U.And(U.IS_LOCKED == "N");
            U.Where(UR.ROLE_CODE.In("Administrator", "Admin", "PowerUser", "User", "Guest"));
            U.Groupby(UR.ROLE_CODE);
            U.Having(U.USER_ACCOUNT.Count > 2);
            U.OrderbyAsc(U.USER_ACCOUNT.Count);
            data = U.Select(U.USER_ACCOUNT.Count.As("USER_COUNT"), UR.ROLE_CODE);
            return data;

        }
        [Demo(Demo = FuncType.Select, MethodName = "GroupByHaving", MethodDescript = "分组统计")]
        public static object GroupByHaving()
        {
            ODAContext ctx = BizContext.GetContext();
            var U = ctx.SYS_USER();
            var UR = ctx.SYS_USER_ROLE();
            var data = U.InnerJoin(UR, U.USER_ACCOUNT == UR.USER_ACCOUNT, UR.STATUS == "O")
               .Where(U.STATUS == "O", UR.ROLE_CODE.In("Administrator", "Admin", "PowerUser", "User", "Guest"))
               .Groupby(UR.ROLE_CODE)
               .Having(U.USER_ACCOUNT.Count > 2)
               .OrderbyAsc(UR.ROLE_CODE,U.USER_ACCOUNT.Count)
               .Select(U.USER_ACCOUNT.Count.As("USER_COUNT"), UR.ROLE_CODE);
            return data;
        }

       
        [Demo(Demo = FuncType.Select, MethodName = "IN/NOT IN", MethodDescript = "IN 条件")]
        public static object In()
        {
            ODAContext ctx = BizContext.GetContext();
            var RA = ctx.SYS_ROLE_AUTHORIZATION();
            var RS = ctx.SYS_RESOURCE(); 
            ///IN 数组
            RA.Where(RA.IS_FORBIDDEN == "N", RA.STATUS == "O", RA.ROLE_CODE.In("Administrator", "Admin", "PowerUser"));

            ///IN 子查询
            var data = RS.Where(RS.STATUS == "O", RS.ID.In(RA, RA.RESOURCE_ID)) 
                .SelectM(); 
            return data;  
        }

        [Demo(Demo = FuncType.Select, MethodName = "Exists/NOT Exists", MethodDescript = "Exists 子查询")]
        public static object Exists()
        {
            ODAContext ctx = BizContext.GetContext();
            var RA = ctx.SYS_ROLE_AUTHORIZATION(); 
            //Exists 子查询的条件
            var RS = ctx.SYS_RESOURCE();
            RA.Where( RA.STATUS == "O", RA.RESOURCE_ID == RS.ID); 

            var data = RS.Where(RS.STATUS == "O", RS.Function.Exists(RA, RA.AllColumn)) 
                .SelectM();
            return data;
        }

        [Demo(Demo = FuncType.Select, MethodName = "Recursion", MethodDescript = "递归查询")]
        public static object Recursion()
        {
            ///如Oracle的StartWith ConnectBy语句一致。ODA处理：先以 where 条作查出需要递归筛先的数据，然后在内存中递归筛选
            ///由于是在内存递归，所以递归使所用到的所有字段必须包含在 Seclect 字段里。
            ///注：ODA性能比 oracle 数据库的 StartWith ConnectBy 差一个等级，但比 SQLServer 的 with as 好一个级等。
            ///递归有深度限制，数据量多的时候性能下降很快，最好保被递归筛选的数在10W条以内

            ODAContext ctx = BizContext.GetContext();

            ////由根向叶子递归 Prior 参数就是递归方向
            SysResource RS = ctx.SYS_RESOURCE();  
            var rlt = RS.Where(RS.STATUS == "O", RS.RESOURCE_TYPE == "MENU")
                .StartWithConnectBy(RS.RESOURCE_NAME.ColumnName + "='根菜单'", RS.PARENT_ID.ColumnName, RS.ID.ColumnName, "MENU_PATH", "->", 10)
                .Select(RS.RESOURCE_NAME.As("MENU_PATH"), RS.ID, RS.PARENT_ID, RS.RESOURCE_NAME, RS.RESOURCE_TYPE, RS.RESOURCE_SCOPE, RS.RESOURCE_LOCATION, RS.RESOURCE_INDEX);
           
            ////由叶子向根递归,Prior 参数就是递归方向
            SysResource RS1 = ctx.SYS_RESOURCE(); 
            var rlt1 = RS.Where(RS.STATUS == "O", RS.RESOURCE_TYPE == "MENU")
                .StartWithConnectBy(RS.RESOURCE_NAME.ColumnName + "='菜单1'", RS.ID.ColumnName, RS.PARENT_ID.ColumnName, "MENU_PATH", "<-", 10)
                .Select(RS.RESOURCE_NAME.As("MENU_PATH"), RS.ID, RS.PARENT_ID, RS.RESOURCE_NAME, RS.RESOURCE_TYPE, RS.RESOURCE_SCOPE, RS.RESOURCE_LOCATION, RS.RESOURCE_INDEX);
            rlt1.Merge(rlt);
            return rlt1;
        }
 
        [Demo(Demo = FuncType.Select, MethodName = "Lambda", MethodDescript = "Lambda语法支持")]
        public static object Lambda()
        {
            ///Lambda语法支持最多九个表连接查询
            int total = 0;
            var data = new ODAContext().From<SysUser>()
                .InnerJoin<SysUserRole>((u, ur) => u.USER_ACCOUNT == ur.USER_ACCOUNT & ur.STATUS == "O")
                 .InnerJoin<SysRole>((u, ur, r) => ur.ROLE_CODE == r.ROLE_CODE & r.STATUS == "O")
                 .InnerJoin<SysRoleAuthorization>((u, ur, r, ra) => r.ROLE_CODE == ra.ROLE_CODE & ra.IS_FORBIDDEN == "O" & ra.STATUS == "O")
                 .Where((u, ur, r, ra) => u.STATUS == "O" & (r.ROLE_CODE == "Administrator" | r.ROLE_CODE == "Admin") & u.IS_LOCKED == "N")
                 .Groupby((u, ur, r, ra) => new[] { r.ROLE_CODE, u.USER_ACCOUNT })
                 .Having((u, ur, r, ra) => ra.RESOURCE_ID.Count > 10)
                 .OrderbyAsc((u, ur, r, ra) => new[] { ra.RESOURCE_ID.Count })
                 .Select(0, 20, out total, (u, ur, r, ra) =>new[] { r.ROLE_CODE, u.USER_ACCOUNT, ra.RESOURCE_ID.Count.As("ResourceCount") });
            return data;
        }

        [Demo(Demo = FuncType.Select, MethodName = "LambdaList", MethodDescript = "简单内连接语法支持")]
        public static object LambdaList()
        {
            ///Lambda语法支持最多九个表连接查询
            int total = 0;
            var data = new ODAContext().From<SysUser, SysUserRole, SysRole, SysRoleAuthorization>()
                 .Where((u, ur, r, ra) => u.USER_ACCOUNT == ur.USER_ACCOUNT & ur.STATUS == "O"
                 & ur.ROLE_CODE == r.ROLE_CODE & r.STATUS == "O"
                 & r.ROLE_CODE == ra.ROLE_CODE & ra.IS_FORBIDDEN == "O" & ra.STATUS == "O"
                 & u.STATUS == "O" & (r.ROLE_CODE == "Administrator" | r.ROLE_CODE == "Admin") & u.IS_LOCKED == "N")
                 .Groupby((u, ur, r, ra) => new[] { r.ROLE_CODE, u.USER_ACCOUNT })
                 .Having((u, ur, r, ra) => ra.RESOURCE_ID.Count > 10)
                 .OrderbyAsc((u, ur, r, ra) => new[] { ra.RESOURCE_ID.Count })
                 .Select(0, 20, out total, (u, ur, r, ra) => new[] { r.ROLE_CODE, u.USER_ACCOUNT, ra.RESOURCE_ID.Count.As("ResourceCount") });
            return data;
        }
    }

    public class UserDefineModel
    {
        public string UserAccount{get;set;}
        public string UserName { get; set; }
        public string Email { get; set; }
        public string Role { get; set; }
        public string RoleName { get; set; }
        public decimal? Times { get; set; }
        public DateTime? DateNow { get; set; }
    }

    public enum TestEnum
    {
        e1,
        e2,
        e3,
        e4,
    }
}
